IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProtocolVersionQuestionnaireAnswers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetProtocolVersionQuestionnaireAnswers]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO  
/******************************************************************  
* Name:   GetProtocolVersionQuestionnaireAnswers  
* Purpose:  Retrieves a list of QuestionnaireAnswer based on the ProtocolVersions's  
*     id passed in as a parameter.  
*  
* PARAMETERS  
* Name      Description       
* -------------   -------------------------------------------  
* @protocolVersionId The Id of the ProtocolVersion whose   
*   QuestionnaireAnswers are to be retrieved.  
* @runPrintOrder: the flag indicating whether to get the print order or current protocol
*   by deault no print order should returned, it is only for certain processes in animal module
******************************************************************/  
  
CREATE Procedure [dbo].[GetProtocolVersionQuestionnaireAnswers] (  
  @protocolVersionId  INTEGER   
  ,@printTypeKey VARCHAR (20) = null
)  
AS  
BEGIN  
  
 SET NOCOUNT ON  
  
 Select QA.Id,  
  QA.QuestionnaireVersionId,   
  QA.Name,  
  QA.Required,  
  QA.Editable,  
  Q.Category as 'QuestionnaireCategory',  
  QA.LastModifiedDate,  
  QA.ExternalId,  
  QA.IsClone,  
  QA.ActionTypeId,  
  QA.Highlight,
  QA.LastModifiedByAdmPersonId,
  ADP.LastName+', '+ADP.FirstName as LastModifiedBy,
  Q.Id as 'QuestionnaireID'
	INTO #temp_PrintOrder
 FROM dbo.QuestionnaireAnswer QA  
  INNER JOIN dbo.QuestionnaireVersion QV   
   on QA.QuestionnaireVersionId = QV.ID  
  INNER JOIN dbo.Questionnaire Q  
   on QV.QuestionnaireId = Q.Id  
  INNER JOIN dbo.ProtocolVersion_QuestionnaireAnswer_Map map  
   on (map.QuestionnaireAnswerId = QA.Id and map.ProtocolVersionId = @protocolVersionId)  
  LEFT JOIN dbo.RefCodeAttribute RCA ON RCA.RefCode = Q.Category AND RCA.Attribute = 'ORDER'
  LEFT JOIN dbo.AdmPerson ADP
   on ADP.Id = QA.LastModifiedByAdmPersonId
 order by RCA.Value, Q.CategorySequenceNo
   
	IF @printTypeKey IS NOT Null
	BEGIN
		SELECT tpo.* FROM #temp_PrintOrder tpo
		JOIN dbo.Questionnaire_PrintOrder_Map apo
		ON tpo.QuestionnaireID = apo.QuestionnaireId
			and apo.PrintTypeKey = @printTypeKey 
		where apo.PrintOrder IS NOT NULL
		ORDER BY apo.printOrder
	END
	ELSE
		SELECT * FROM #temp_PrintOrder

END  
  
  

